Creating data sources for different database in cognos

 Creating data sources for the different databases: 

Oracle:

• Open the browser internet explorer enter the following URL
• http:/local host/cognos 10
• from welcome page select administrator IBM cognos content
• select configuration  select near data source(database symbol)
• create name next  type oracle next sql net connect string ‘ORCL’ user id password  select test the connection
• Click on test  close next finish
• Create a name project with the name by B8M_scott_project and import the tables from scott database
• Open framework manager  select create a name project 
• Enter project name B8m_scoot_projectclick on ok select English select datasource  next  select B8m_oracle_datasource  click on next select scott expand tables select EMP,Dept next uncheck user primary foreign keys click on import finish

Procedure to create a relationship between the queries  

Subject at model level by using FM:

Note: There should be at least one common item between the query subjects to create relationship

• From project viewer pan, expand the namespace
• Select the query subjects between them relation to be created by using control button
• Right click any selected query subjects create relationship
• Select dept no from both sides (since dept no is the common item in emp and dept tables)
• Dept side 1.1 and emp side 1.n  than click on ok

Creating a name data source for sql server:

• Open the browser internet explorer enter the following URL
• http:/local host/ IBM cognos
• from welcome page IBM cognos content--> select configuration
• select name data source  namenext  type Microsoft SQL server (OLIBP) nextserver name local host  database name (go sal)
• password  username_sa user id_sa test finish

Model calculations:

• FM allows the users to define name calculation objects from already existing objects.
• Model calculations are reusable

EX: derive a name calculation object with the name plane_name by multiplying quality and unit sal price

• Right click on name spacecreate select calculation name plane_revenuedefine the following expression the expression defines windows
• (quantity+until sale price) quantity and unit sal price available in order_details

EX: 2: derive a name calculation object in the name staff_full name by calculating by first name and last name

• Right click on the name space  create  select calculation  name stoff  full name define the following expression

Embedded calculation:

It is a process of enhancing the query subject by adding the name query items Ex: add a name query item with the name plane_ revenue to the query subject order_details

• Right click on order_details query subject  select edit definition
• Select the default query  define the following query in SQL windows as shown bellow
• Select the query subject column drag and drop SQL windows

Deriving name query subject by merging multiple query subjects: 

Note: there should be at least one common column between the query subjects to merge EX: merge the query subjects product and product_forecast to derive product_merge From name space , select the both the query subjects by using ctrl button right click on any selected query subject  select merge in name query subject yes right click rename enter product_merge enter.  

Creating name query subjects:

There are three types of name queries

1. Model (query subject and query item)
2. Data source(tables and columns)
3. Stored procedure

Creating a new query subject by using model as a source:

• Right click on the name space create query subject
• Enter the name country _model_query subject model  ok
• Drg country , flag image from country and drop in query item window and required columns  drag all three query subjects column ok

Creating a data source for oracle:

• Open the browser internet explorer enter the following URL
• http:/local host/ IBM cognos/
• select configuration select near data source
• enter the name oracle_data source next
• type  oracle  next orcl user id password
• enter user id scott password tiger and conform password tiger
• click test the configuration  testclose next  finish

EX: create a name project with the name scott_project and import tables emp and dept with out relation

• create file temp

ex:  creating a name query subject by using data source as a source

• right click on the name space create  query subject
• name product_data source_QS datasource(table and columns)
• click on ok selects go sales next expand go sal
• expand tables productdrag the columns product_forcast
• drag the columns in the inclided columns window
• click on next  next  next click on test sample click on finish

DMR model (dimensionally modeled relations):

• use dmr models to perform multidimensional analysis

Multidimensional analysis:

It is a process of viewing the data at different levels of details

• use the following features to perform multidimensional analysis
  • Drill up: To navigate from lowest level to highest level  
  • Drill down: To navigate from highest level to lowest level  
  • Creating DMR model: Creating DMR model is nothing but creating regular dimensions and measure dimensions
• the source for creating DMR model related url database meta data
• a DMR model is made up of regular dimensions and measure dimensions

Regular dimensions:

• A regular dimension is made up of hierarchies
• A hierarchy is made up of levels
• Each and every level should be assigned with the following rules

1. _business key
2. _member caption
3. _member description

Note: Business key and caption are mandatory but description is optional  

Note: A regular dimension can have any number of hierarchies A hierarchy can have any number of levels  

Measure dimension:

It is created with all the measures (facts or KPI’s) required to perform multi dimensional analysis (drill up and drill down)  

Scope relationship:

It is a relation between regular dimension and measure dimension

• By default cognos creates scope relation as soon as regular dimension and measure dimension are created

Procedure to create regular dimension:

• Open frame work manager with the required project
• Right click on the namespace (model), create regular dimension
• From hierarchies window, select add hierarchy  select rename enter the name “ product hierarchy”click on enter
• Right click on name hierarchy (all) select rename enter the name “produce(call)”enter
• Select add level right click on name level sheet rename  enter the new name “product_line”  enter
• Expand product _line query subject from the name space
• Drag product_line_code, drop on product_line level hierarchy
• Select business key, drag product_line.ex,drop on product line level hierarchyselect th member option
• Select unique level
• Select add levelrename new level as “product _type”
• Click on ok right click on regular dimension select rename “product_regular_dimension”

Produce to create measure dimension:

• Right click on name spacecreate select measure dimension expand order_details query subject sheet all the required measures and drop on measures and down
• Expand invent_level drag carrying_inventory and drop on measures window  click on ok
• Rename the measure dimension as sales_measure_dimension
• Click on diagram tab

For indepth understanding Cognos Training click on

• Cognos Tutorials
• Introduction to IBM COGNOS
• Dimensions & OLAP Types in Cognos
• Fitters and its types in Cognos
• Schemas and its types in Cognos